﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetStaffInRole]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetStaffInRole];
GO
CREATE PROCEDURE [dbo].[sproc_GetStaffInRole] 
    @RoleID int
/*

============================================================
功能:    得到所有在@RoleID角色中的人员
参数:
        @RoleID int        :    角色ID
============================================================

*/
AS 
BEGIN
SET NOCOUNT ON

Select
		a.*,
		(
			case 
				when a.birthday is NULL then '-'
				when datediff(dd,a.birthday,'1900-01-01')=0 then '-'
				else convert(nvarchar,datediff(yy,a.birthday,getdate()))
			end
		) as Age,
		(case sex when 1 then N'男' else N'女' end) as SexName,
		Convert(nvarchar(10),A.RegistedDate,120) AS RQ,
		(SELECT Position_name FROM dbo.uds_Position WHERE Position_id = c.Position_id) AS Position_Name
    From
		(
			select *
			from dbo.uds_staff 
			Where staff_id in (select staff_id from uds_staff_in_role where role_id = @roleid) and dimission = 0
        )a,
        dbo.uds_Position b,
        dbo.uds_staff_in_position c
    WHERE
		c.Position_id = b.Position_id
		and a.staff_id = c.staff_id    
    order by
		a.staff_name

END
GO